﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Infragistics.Excel;

namespace Models
{
    //Lưu trữ tiến trình update
    static public class LogEvent
    {
        static public DataTable Log = new DataTable();
    }

    #region <Class DataLib> 'Giao Tiếp DataBase Sql'
    public class DataModel
    {
        #region Property
        //static public SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Data\\QLKS.mdf;Integrated Security=True;User Instance=True");
        //Data Source=.\\SQLEXPRESS;AttachDbFilename=Data\\QLKS.mdf;Integrated Security=True;User Instance=True");
        public SqlConnection conn = new SqlConnection();
        public DataSet ds = new DataSet();
        #endregion

        #region Melthod

        public DataModel()
        {
            conn = new SqlConnection();
        }
        //Create Connection
        public DataModel(string server, bool Authentication, string catalog, string login, string password)
        {
            conn.ConnectionString = @"Data Source=" + server + ";Initial Catalog=" + catalog;
            if (Authentication == true)
                conn.ConnectionString += ";User ID=" + login + ";Password=" + password;
            else
                conn.ConnectionString += ";Integrated Security=True";
            conn.ConnectionString += ";Connection Timeout=3";
        }

        //Check Connection
        public bool CheckConnection()
        {
            try
            {
                conn.Open();
                conn.Close();
            }
            catch { return false; }
            return true;
        }

        //Create Connection
        public void CreateConnection(string server, bool Authentication, string catalog, string login, string password)
        {
            conn.ConnectionString = @"Data Source=" + server + ";Initial Catalog=" + catalog;
            if (Authentication == true)
                conn.ConnectionString += ";User ID=" + login + ";Password=" + password;
            else
                conn.ConnectionString += ";Integrated Security=True";
        }

        //Connection
        public bool Connection(string server,bool Authentication, string catalog, string login, string password)
        {
            CreateConnection(server, Authentication, catalog, login, password);
            return CheckConnection();
        }

        //Query Result
        public DataSet QueryResults(string query)
        {
            if(conn.State == ConnectionState.Closed)
                conn.Open();
            DataSet ds = new DataSet();
            SqlCommand comm = new SqlCommand(query, conn);
            SqlDataAdapter adp = new SqlDataAdapter(comm);            
            adp.Fill(ds);
            conn.Close();
            if (ds.Tables.Count <= 0)
                return new DataSet();
            return ds;
        }

        //Run Execute
        public void RunExecute(string query)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            SqlCommand comm = new SqlCommand(query, conn);
            LogEvent.Log.Rows.Add(query);
            comm.ExecuteNonQuery();
            conn.Close();
        }

        //Ghép 2 table
        static public DataTable MixTables(DataTable tb1, DataTable tb2)
        {
            foreach (DataRow row in tb2.Rows)
                tb1.Rows.Add(row);
            return tb1;
        }

        //Export DataTable To Excel
        static public bool ExportToExcel(DataTable tb, string fields, string title)
        {
            try
            {
                Workbook wb = new Workbook();
                Worksheet ws;

                int iRow = 3;
                int iCell = 1;
                string[] field = fields.Split(';');
                ws = wb.Worksheets.Add("Sheet1");
                WorksheetMergedCellsRegion CellMerge = ws.MergedCellsRegions.Add(1, 1, 2, field.Length);
                CellMerge.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                CellMerge.CellFormat.Font.Italic = ExcelDefaultableBoolean.False;
                //CellMerge.CellFormat.FillPatternForegroundColor = System.Drawing.Color.Blue;
                CellMerge.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                CellMerge.Value = title;
                CellMerge.CellFormat.Font.Height = 2000;

                int nRow = tb.Rows.Count;

                //System.Windows.Threading.Dispatcher.CurrentDispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Background,
                //                       new System.Threading.ThreadStart(delegate {
                foreach (string f in field)
                {
                    string[] s = f.Split(':');
                    ws.Rows[iRow].Cells[iCell].CellFormat.Alignment = HorizontalCellAlignment.Center;
                    ws.Columns[iCell].Width = int.Parse(s[2]) * 40;
                    ws.Rows[iRow].Cells[iCell].Value = s[1];
                    ws.Rows[iRow++].Cells[iCell].CellFormat.Font.Color = System.Drawing.Color.Blue;
                    for (int i = 0; i < nRow; i++)
                        ws.Rows[iRow + i].Cells[iCell].Value = tb.Rows[i][s[0]].ToString();
                    iCell++;
                    iRow = 3;
                }
                //}));
                //Mở Dialog SaveFile
                // wb.Save(fileName);
                System.Windows.Forms.SaveFileDialog sFile = new System.Windows.Forms.SaveFileDialog();
                sFile.Filter = "Microsoft Excel 2007|*.XLS";
                sFile.InitialDirectory = System.Windows.Forms.Application.StartupPath;
                sFile.Title = "Save as Excel File";

                if (sFile.ShowDialog() == System.Windows.Forms.DialogResult.OK && sFile.FileName != "")
                    wb.Save(sFile.FileName);
                else
                    return false;
                return true;
            }
            catch { return false; }
        }

        //Trộn 2 table
        static public DataTable MixTable(DataTable tb1, DataTable tb2)
        {
            if (tb1.Rows.Count <= 0)
                return tb2;
            foreach (DataRow row in tb2.Rows)
                tb1.Rows.Add(row.ItemArray);
            return tb1.Copy();
        }
        #endregion
    }
    #endregion
}
